USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_schema_compare') BEGIN
	DROP PROCEDURE tsql.sp_schema_compare
	PRINT '<<< DROP PROCEDURE tsql.sp_schema_compare - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.sp_schema_compare
Type:			User defined procedure
Owner:						
Description:	compares the schema between two tables and shows the differences
Parameters:		@ref_db = name of the database where reference table resides
				@ref_schema = name of the schema for the reference table
				@ref_table = reference table name
				@cmp_db = name of the database where compare table resides
				@cmp_schema = name of the schema where compare tbale resides
				@cmp_table = compare tbale name
				@options = the level of comparission to be performed:
							0 - return all information including matching columns
							1 - return only differences						
 				@debug = development only:
							0 - live mode 
							1 - debug (display internal values instead of result set)

Dependens on:  	none
Usage:			
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=sp_schema_compare&referringTitle=Documentation		
Version:		20120822
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/20/2005 - Adrian E Dudau - Procedure created
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
================================================================================================*/

CREATE PROCEDURE [tsql].sp_schema_compare 
(
  @ref_db AS SYSNAME = ''
, @ref_schema AS SYSNAME = 'dbo'  
, @ref_table AS SYSNAME 
, @cmp_db AS SYSNAME = ''
, @cmp_schema AS SYSNAME = 'dbo'
, @cmp_table AS SYSNAME = ''  
, @options AS BIT = 0 
, @debug BIT = 0 
) 

AS

BEGIN

	DECLARE @SQL NVARCHAR(MAX); 
	DECLARE @ErrMsg VARCHAR(2000);
	DECLARE @Cnt AS BIGINT 
	DECLARE @CntInr AS BIGINT 


	SET @SQL = '' 
	
	-- Check Input parameters
	-- verify db names
	IF LEN(ISNULL(@ref_db,'')) = 0
		SET @ref_db = DB_NAME(); 
	ELSE
		SET @ref_db = REPLACE(REPLACE(@ref_db,'[',''),']',''); 
	
	IF LEN(ISNULL(@cmp_db,'')) = 0
		SET @cmp_db = DB_NAME(); 
	ELSE
		SET @cmp_db = REPLACE(REPLACE(@cmp_db,'[',''),']',''); 
	

	-- Dynamic SQL used to change the Db context accordingly 
	-- check if ref table exists 
	--SET @SQL = 'SELECT @CntInr = COUNT(*) FROM [' + @ref_db + '].INFORMATION_SCHEMA.TABLES T WHERE TABLE_NAME = ''' + @ref_table + ''' AND TABLE_SCHEMA = ''' + @ref_schema + ''''; 
	--EXECUTE sp_executesql @SQL, N'@CntInr BIGINT OUTPUT',@CntInr = @Cnt OUTPUT;
	--IF @Cnt = 0 BEGIN 
	--	SET @ErrMsg = 'Specified Table: ' + @ref_db + '.' + @ref_schema + '.' + @ref_table + ' does not exist!!';
	--	RAISERROR (@ErrMsg , 16, 1)
	--	RETURN  
	--END

	---- check if compare table exists 
	--SET @SQL = 'SELECT @CntInr = COUNT(*) FROM [' + @cmp_db + '].INFORMATION_SCHEMA.TABLES T WHERE TABLE_NAME = ''' + @cmp_table + ''' AND TABLE_SCHEMA = ''' + @cmp_schema + ''''; 
	--EXECUTE sp_executesql @SQL, N'@CntInr BIGINT OUTPUT',@CntInr = @Cnt OUTPUT;
	--IF @Cnt = 0 BEGIN 
	--	SET @ErrMsg = 'Specified Table: ' + @cmp_db + '.' + @cmp_schema + '.' + @cmp_table + ' does not exist!!';
	--	RAISERROR (@ErrMsg , 16, 1)
	--	RETURN  
	--END
	

-- Begin 
	SET @SQL = @SQL + ' SELECT 	ref_col ' 
	SET @SQL = @SQL + 		' , ref_data_type '
	SET @SQL = @SQL + 		' , ref_data_size ' 
	SET @SQL = @SQL + 		' , ref_is_nullable '
	SET @SQL = @SQL + 		' , cmp_col '
	SET @SQL = @SQL + 		' , cmp_data_type '
	SET @SQL = @SQL + 		' , cmp_data_size'
	SET @SQL = @SQL + 		' , cmp_is_nullable ' 
	SET @SQL = @SQL + 		' , CASE WHEN DifferenceDetails <> ''New Column'' AND DifferenceDetails <> ''Deleted Column'' AND LEN(ISNULL(DifferenceDetails, '''')) > 0 THEN ' 
	SET @SQL = @SQL + 		' ''Changed Column: '' ELSE '''' END + DifferenceDetails AS DifferenceDetails' 
	SET @SQL = @SQL + ' FROM ( ' 
	SET @SQL = @SQL + 			' SELECT 	    REF.COLUMN_NAME AS ref_col ' 
	SET @SQL = @SQL + 					' , REF.IS_NULLABLE AS ref_is_nullable ' 
	SET @SQL = @SQL + 					' , REF.DATA_TYPE AS ref_data_type ' 
	SET @SQL = @SQL + 					' , REF.DATA_SIZE AS ref_data_size '
	SET @SQL = @SQL + 					' , CMP.COLUMN_NAME AS cmp_col '
	SET @SQL = @SQL + 					' , CMP.DATA_TYPE AS cmp_data_type ' 
	SET @SQL = @SQL + 					' , CMP.IS_NULLABLE AS cmp_is_nullable '
	SET @SQL = @SQL + 					' , CMP.DATA_SIZE  AS cmp_data_size '
	SET @SQL = @SQL + 					' , CASE  WHEN LEN(ISNULL(REF.COLUMN_NAME,'''')) = 0 THEN ''Additional Column'' '
	SET @SQL = @SQL + 						' WHEN LEN(ISNULL(CMP.COLUMN_NAME,'''')) = 0 THEN ''Missing Column'' '
	SET @SQL = @SQL + 						' WHEN ISNULL(REF.COLUMN_NAME,'''') = ISNULL(CMP.COLUMN_NAME,'''') THEN '
	SET @SQL = @SQL + 							' LTRIM(RTRIM(CASE WHEN REF.DATA_TYPE <> CMP.DATA_TYPE THEN '' DataType changed to '' + UPPER(CMP.DATA_TYPE) ELSE '''' END '
	SET @SQL = @SQL + 							' + CASE WHEN REF.DATA_SIZE <> CMP.DATA_SIZE THEN '' DataSize changed to '' + UPPER(CMP.DATA_SIZE) ELSE '''' END '
	SET @SQL = @SQL + 							' + CASE WHEN REF.IS_NULLABLE > CMP.IS_NULLABLE THEN '' Nullable Removed'' '
	SET @SQL = @SQL + 						' WHEN REF.IS_NULLABLE < CMP.IS_NULLABLE THEN '' Nullable Added'' '
	SET @SQL = @SQL + 							' ELSE '''' END)) '
	SET @SQL = @SQL + 						' ELSE '''' ' 
	SET @SQL = @SQL + 					' END AS DifferenceDetails '
	SET @SQL = @SQL + 			' FROM   ( '	
	SET @SQL = @SQL + 					' SELECT UPPER(RTRIM(LTRIM(COLUMN_NAME))) AS COLUMN_NAME ' 
	SET @SQL = @SQL + 							' , IS_NULLABLE ' 
	SET @SQL = @SQL + 							' , UPPER(RTRIM(LTRIM(DATA_TYPE))) AS DATA_TYPE ' 
	SET @SQL = @SQL + 							' , ISNULL(CAST(CHARACTER_OCTET_LENGTH AS varchar), '''') ' 
	SET @SQL = @SQL + 								' + ISNULL(CAST(NUMERIC_PRECISION AS varchar), '''') '
	SET @SQL = @SQL + 								' + CASE WHEN (NUMERIC_SCALE IS NULL) THEN '''' ELSE ''.'' END '
	SET @SQL = @SQL + 								' + ISNULL(CAST(NUMERIC_SCALE AS VARCHAR), '''') AS DATA_SIZE '
	SET @SQL = @SQL + 					' FROM [' + @ref_db + '].INFORMATION_SCHEMA.COLUMNS ' 
	SET @SQL = @SQL + 					' WHERE TABLE_NAME = ''' + @ref_table + ''' AND TABLE_SCHEMA = ''' + @ref_schema + ''''
	SET @SQL = @SQL + 				' ) REF '
	SET @SQL = @SQL + 			' FULL OUTER JOIN ' 
	SET @SQL = @SQL + 			'  ( ' 
	SET @SQL = @SQL + 					' SELECT UPPER(RTRIM(LTRIM(COLUMN_NAME))) AS COLUMN_NAME ' 
	SET @SQL = @SQL + 							' , IS_NULLABLE ' 
	SET @SQL = @SQL + 							' , UPPER(RTRIM(LTRIM(DATA_TYPE))) AS DATA_TYPE ' 
	SET @SQL = @SQL + 							' , ISNULL(CAST(CHARACTER_OCTET_LENGTH AS varchar), '''') ' 
	SET @SQL = @SQL + 								' + ISNULL(CAST(NUMERIC_PRECISION AS varchar), '''') '
	SET @SQL = @SQL + 								' + CASE WHEN (NUMERIC_SCALE IS NULL) THEN '''' ELSE ''.'' END '
	SET @SQL = @SQL + 								' + ISNULL(CAST(NUMERIC_SCALE AS VARCHAR), '''') AS DATA_SIZE '
	SET @SQL = @SQL + 					' FROM [' + @cmp_db + '].INFORMATION_SCHEMA.COLUMNS '
	SET @SQL = @SQL + 					' WHERE TABLE_NAME = ''' + @cmp_table + ''' AND TABLE_SCHEMA = ''' + @cmp_schema + ''''
	SET @SQL = @SQL + 			' ) CMP '  
	SET @SQL = @SQL +			 ' ON REF.COLUMN_NAME = CMP.COLUMN_NAME '
	SET @SQL = @SQL + 		' ) AS FTBL '

-- Dynamic parameters 
	IF ISNULL(@options,0) > 0 
		SET @SQL = @SQL + ' WHERE LEN(ISNULL(DifferenceDetails, '''')) > 0 '

	IF @DEBUG = 1 
	BEGIN 
		PRINT '@ref_db(IN) = ' + ISNULL(@ref_db, '')
		PRINT '@ref_table(IN) = ' + ISNULL(@ref_table,'')
		PRINT '@cmp_db(IN) = ' + ISNULL(@cmp_db,'')
		PRINT '@cmp_table(IN) = ' + ISNULL(@cmp_table,'')
		PRINT '(optional IN params) ---------------------------------------------------------------------' 
		PRINT '@options(IN) = ' + CAST(ISNULL(@options,0) AS VARCHAR)
		PRINT '@DEBUG(IN) = ' + CAST(ISNULL(@DEBUG,0) AS VARCHAR) 
		PRINT '(Internal Variables) ------------------------------------------------------------------------' 
		PRINT '@SQL = ' + ISNULL(@SQL,'')
	END
	ELSE
		EXEC (@SQL)


END
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_schema_compare') BEGIN 
	PRINT '<<< CREATE PROCEDURE tsql.sp_schema_compare - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE PROCEDURE tsql.sp_schema_compare - Completed with ERROR >>>'
END


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

--  GRANT SELECT TO PUBLIC will be used at Db level
